﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace Net_SCADA
{
    class DBOperate
    {
        #region 报警相关内容
        public static DataTable his_GetAlarmValues(DateTime StartTime , DateTime EndTime)
        {
            int totaldays = (int)(EndTime.Subtract(StartTime).TotalDays);
            StringBuilder cmd = new StringBuilder();
            for (int i = 0; i <= totaldays; i++)
            {
                if (DBBase.CheckTable("his_alarm_" + StartTime.AddDays(i).ToString("yyyyMMdd")))
                    cmd.Append("SELECT * from `his_alarm_").Append(StartTime.AddDays(i).ToString("yyyyMMdd")).Append("` ")
                    .Append("WHERE `SaveTime` >= '").Append(StartTime.ToString("yyyy-MM-dd HH:mm:ss")).Append("' ")
                    .Append("AND `SaveTime` <= '").Append(EndTime.ToString("yyyy-MM-dd HH:mm:ss")).Append("' ")
                    .Append("union all ");
            }
            if (cmd.Length < 11) return null;
            cmd.Remove(cmd.Length - 11, 11).Append(" order by `SaveTime` desc");
            return DBBase.GetDataTable(cmd.ToString());
        }
        public static bool SaveAlarmLevelSet(Dictionary<int , int[]>AlarmLevelSet)
        {
            if (AlarmLevelSet == null || AlarmLevelSet.Count == 0) return false;
            if (!DBBase.CheckTable("set_alarm")) return false;
            List<string> sqlcmd=new List<string>();
            StringBuilder sb=new StringBuilder();
                foreach (var item in AlarmLevelSet.Keys)
                {
                    sb.Clear();
                    sb.Append("update `set_alarm` set ")
                        .Append("`allevel`='").Append(AlarmLevelSet[item][0]).Append("',")
                        .Append("`alaction`='").Append(AlarmLevelSet[item][1]).Append("' ")
                        .Append("where `alid`='").Append(item).Append("';");
                    sqlcmd.Add(sb.ToString());
                }
            return DBBase.ExecuteSqlTran(sqlcmd);
        }

        public static bool SaveAlarmLevelColorSet(Dictionary<int,Color[]> AlarmLevelColorSet)
        {
            if (AlarmLevelColorSet == null || AlarmLevelColorSet.Count == 0) return false;
            List<string> sqlcmd=new List<string>();
            StringBuilder sb=new StringBuilder();
            if (CreateAlarmLevelColorSetTable())
            {
                foreach (var item in AlarmLevelColorSet.Keys)
                {
                    sb.Clear();
                    sb.Append("update `set_alarm_color` set ")
                        .Append("`albackcolor`='").Append(AlarmLevelColorSet[item][1].ToArgb()).Append("', ")
                        .Append("`alforecolor`='").Append(AlarmLevelColorSet[item][0].ToArgb()).Append("' ")
                        .Append("where `allevel`='").Append(item).Append("';");
                    sqlcmd.Add(sb.ToString());
                }
            }
            return DBBase.ExecuteSqlTran(sqlcmd);
        }

        private static bool CreateAlarmLevelColorSetTable()
        {
            if (DBBase.CheckTable("set_alarm_color")) return true;
            StringBuilder sql = new StringBuilder(@" CREATE TABLE `set_alarm_color` (")
                .Append("`allevel` int(11) NOT NULL,")
                .Append("`albackcolor` int(11) NOT NULL,")
                .Append("`alforecolor` int(11) NOT NULL,")
                .Append("`isuse` int(11) NOT NULL")
                .Append(") ENGINE = MyISAM DEFAULT CHARSET=utf8;");
            return DBBase.CreateTable(sql.ToString());
        }

        public static DataTable GetStatusSet()
        {
            if (DBBase.CheckTable("set_status"))
            {
                return DBBase.GetDataTable(@"Select * from `set_status` where `equipid`!='' and `alName`!='' order by `alID`");
            }
            return null;
        }

        //保存事件信息到数据库
        public static bool SaveAlarmData(List<string[]> alDetails)
        {
            string tablename="his_alarm_"+DateTime.Now.Date.ToString("yyyy");
            List<string> sqls=new List<string>();
            if (CreateAlarmDataTable(tablename))
            {

                foreach (string[] item in alDetails)
                {
                    StringBuilder sb_cmd = new StringBuilder("INSERT INTO `" + tablename + "` (`SaveTime`,`AlarmName`,`AlarmActive`,`AlarmLevel`,`AlarmID`");
                    StringBuilder sb_val = new StringBuilder(")VALUES(");
                    for (int i = 0 ; i < item.Length ; i++)
                    {
                        sb_val.Append("'").Append(item[i]).Append("',");
                    }
                    sqls.Add(sb_cmd.Append(sb_val.ToString(0 , sb_val.Length - 1)).Append(");").ToString());
                }
                return DBBase.ExecuteSqlTran(sqls);
            }
            return false;
        }
        public static bool SaveAlarmDataByDay(List<string[]> alDetails)
        {
            string tablename = "his_alarm_" + DateTime.Now.Date.ToString("yyyyMMdd");
            List<string> sqls = new List<string>();
            if (CreateAlarmDataTable(tablename))
            {

                foreach (string[] item in alDetails)
                {
                    StringBuilder sb_cmd = new StringBuilder("INSERT INTO `" + tablename + "` (`SaveTime`,`AlarmName`,`AlarmActive`,`AlarmLevel`,`AlarmID`");
                    StringBuilder sb_val = new StringBuilder(")VALUES(");
                    for (int i = 0; i < item.Length; i++)
                    {
                        sb_val.Append("'").Append(item[i]).Append("',");
                    }
                    sqls.Add(sb_cmd.Append(sb_val.ToString(0, sb_val.Length - 1)).Append(");").ToString());
                }
                return DBBase.ExecuteSqlTran(sqls);
            }
            return false;
        }

        private static bool CreateAlarmDataTable(string table)
        {
            if (DBBase.CheckTable(table)) return true;
            StringBuilder sql = new StringBuilder(@"CREATE TABLE IF NOT EXISTS `" + table + "` (");
            sql.Append(@"`SaveTime` varchar(255) NOT NULL,");
            sql.Append(@"`AlarmName` varchar(255) NOT NULL,");
            sql.Append(@"`AlarmActive` int(1) NOT NULL,");
            sql.Append(@"`AlarmLevel` int(8) NOT NULL,");
            sql.Append(@"`AlarmID` int(8) NOT NULL");
            sql.Append(@") COLLATE 'utf8_general_ci';");
            return DBBase.CreateTable(sql.ToString());
        }
        #endregion

        #region 零件信息
        public static DataTable GetProductInfo(string ProductVersion = "")
        {
            StringBuilder cmd;
            if (ProductVersion != "")
                cmd = new StringBuilder(@"SELECT * FROM pro_ProductInfo WHERE isUsable='1' and ProductVersion = '").Append(ProductVersion).Append("';");
            else
                cmd = new StringBuilder(@"SELECT * FROM pro_ProductInfo WHERE isUsable='1' order by ProductVersion;");
            return DBBase.GetDataTable(cmd.ToString());
        }

        public static bool DeleteProductInfo(string ProductVersion)
        {
            StringBuilder cmd=new StringBuilder(@"UPDATE pro_ProductInfo SET isUsable='0',SaveTime='").Append(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")).Append("' WHERE ProductVersion = '").Append( ProductVersion).Append("'");
            DataTable dt=DBBase.GetDataTable(@"Select * from pro_ProductInfo where ProductVersion = '"+ ProductVersion+"'");
            if (dt != null && dt.Rows.Count > 0)//有记录，修改
            {
                if( DBBase.ExecuteSqlTran(new List<string>() { cmd.ToString() }))
                {
                    return DeleteProductProgram(ProductVersion);
                }
                return false;
            }
            return false;
            
        }

        public static bool ModifyProductInfo(Dictionary<string , string> info)
        {
            StringBuilder sb_cmd = new StringBuilder(@"UPDATE pro_ProductInfo SET ");  //isUsable='0',
            foreach (string item in info.Keys)
            {
                sb_cmd.Append(item).Append("='").Append(info[item]).Append("',");
            }
            sb_cmd.Remove(sb_cmd.Length - 1 , 1);
            sb_cmd.Append(" WHERE ProductVersion='").Append(info["ProductVersion"]).Append("';");
            return DBBase.ExecuteSqlTran(new List<string>() { sb_cmd.ToString() });
        }

        public static bool SaveProductAlarmIndex(string alarmindex)
        {
            return true;
        }
        public static bool SaveProductInfo(Dictionary<string ,string> info)
        {
            if (CreateProductInfoTable())
            {
                StringBuilder sb_cmd = new StringBuilder("INSERT INTO `pro_ProductInfo` (`SaveTime`,");
                StringBuilder sb_val = new StringBuilder(")VALUES('"+DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")+"',");
                foreach (string item in info.Keys)
                {
                    sb_cmd.Append("`").Append(item).Append("`,");
                    sb_val.Append("'").Append(info[item]).Append("',");
                }
                sb_cmd.Remove(sb_cmd.Length - 1 , 1);
                // sb_cmd =  new StringBuilder(sb_cmd.ToString(0 , sb_cmd.Length - 1));
                //sb_cmd.Append(sb_val.ToString(0 , sb_val.Length - 1)).Append(");");
                sb_cmd.Append(sb_val.Remove(sb_val.Length - 1 , 1)).Append(");");
                return DBBase.ExecuteSqlTran(new List<string>() { sb_cmd.ToString() });
            }
            return false;
        }
        private  static bool CreateProductInfoTable()
        {
            if (DBBase.CheckTable("pro_ProductInfo")) return true;
            StringBuilder sql = new StringBuilder(@"CREATE TABLE IF NOT EXISTS `pro_ProductInfo` (");
            sql.Append(@"`ProductVersion` varchar(30) NOT NULL,");
            sql.Append(@"`SaveTime` varchar(25) Not NULL,");
            sql.Append(@"`isUsable` int(1) Default 1,");
            sql.Append(@"`info0` varchar(100) NULL,");
            sql.Append(@"`info1` varchar(100) NULL,");
            sql.Append(@"`info2` varchar(100) NULL,");
            sql.Append(@"`info3` varchar(100) NULL,");
            sql.Append(@"`info4` varchar(100) NULL,");
            sql.Append(@"`info5` varchar(100) NULL,");
            sql.Append(@"`info6` varchar(100) NULL,");
            sql.Append(@"`info7` varchar(100) NULL,");
            sql.Append(@"`info8` varchar(100) NULL,");
            sql.Append(@"`info9` varchar(100) NULL,");
            sql.Append(@"`info10` varchar(100) NULL,");
            sql.Append(@"`info12` varchar(100) NULL,");
            sql.Append(@"`info13` varchar(100) NULL,");
            sql.Append(@"`keyalarm` varchar(255) NULL,");
            sql.Append(@"`lampalarm` varchar(255) NULL");
            sql.Append(@") COLLATE 'utf8_general_ci';");
            return DBBase.CreateTable(sql.ToString());
        }
        public static DataTable GetProductInfoTitle()
        {
            string cmd=@"Select * from set_ProductInfoTitle where isuse=1 order by infoID";
            return DBBase.GetDataTable(cmd);
        }
        #endregion

        #region 工艺信息
        public static bool DeleteProductProgram(string ProductVersion)
        {
            StringBuilder cmd=new StringBuilder(@"UPDATE pro_ProductProgram SET isUsable='0',LastModifyTime='").Append(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")).Append("' WHERE ProductVersion = '").Append( ProductVersion).Append("';");
            return DBBase.ExecuteSqlTran(new List<string>() { cmd.ToString() });
        }

        public static DataTable GetProductProgram(string ProductVersion)
        {
            StringBuilder cmd=new StringBuilder(@"Select * from pro_ProductProgram where ProductVersion = '").Append(ProductVersion).Append("' and isUsable=1;");
            return DBBase.GetDataTable(cmd.ToString());
        }

        public static bool ChangeProductAlarmSelection(string ProductVersion,string KeyAlarmSelection,string LampAlarmSelection)
        {
            if (DBBase.CheckTable("pro_productinfo"))
            {
                DataTable dt=DBBase.GetDataTable(@"select * from pro_productinfo where ProductVersion='"+ProductVersion+"';");
                if (dt == null || dt.Rows.Count == 0) return false;
                StringBuilder cmd=new StringBuilder(@"UPDATE pro_productinfo SET `keyalarm`='").Append(KeyAlarmSelection).Append("', `lampalarm`='").Append(LampAlarmSelection).Append("' where ProductVersion='").Append(ProductVersion).Append("';");
                return DBBase.ExecuteSqlTran(new List<string>() { cmd.ToString() });
            }
            return false;
        }

        public static bool SaveProductProgram(string ProductVersion,Dictionary<string,string> SetValues)
        {

            if (DBBase.CheckTable("pro_ProductProgram") || CreateProgramTable())
            {
                StringBuilder cmd=new StringBuilder();
                DataTable dt=GetProductProgram(ProductVersion);
                if(dt!=null && dt.Rows.Count > 0)//已经有记录了，修改
                {
                    cmd.Append(@"UPDATE pro_ProductProgram SET ");
                    foreach (string item in SetValues.Keys)
                    {
                        cmd.Append("`").Append(item).Append("`='").Append(SetValues[item]).Append("',");
                    }
                    cmd.Append(@"LastModifyTime='" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "' ");
                    cmd.Append(@"WHERE ProductVersion='" + ProductVersion + "';");
                    return DBBase.ExecuteSqlTran(new List<string>() { cmd.ToString() });
                }
                cmd.Append(@"INSERT INTO `pro_ProductProgram` (`ProductVersion`,`CreateTime`,`LastModifyTime`,");
                StringBuilder v=new StringBuilder(")VALUES('").Append(ProductVersion).Append("',");
                v.Append("'").Append(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")).Append("',");//创建时间
                v.Append("'").Append(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")).Append("',");//修改时间
                foreach (string  item in SetValues.Keys )
                {
                    cmd.Append("`").Append(item).Append("`,");
                    v.Append("'").Append(SetValues[item]).Append("',");
                }
                cmd.Append("`isUsable`");
                v.Append("'1');");
                return DBBase.ExecuteSqlTran(new List<string>() { cmd.Append(v).ToString() });
            }
            return false;
        }

        private static bool CreateProgramTable()
        {
            DataTable dt=DBBase.GetDataTable(@"Select `zoneid` from set_zone where `groupid`>0 and `issv`=1 and `svtype`<10 order by zoneID");
            if (dt == null || dt.Rows.Count == 0) return false;

            StringBuilder sql = new StringBuilder(@"CREATE TABLE IF NOT EXISTS `pro_ProductProgram` (");
            sql.Append(@"`ProductVersion` varchar(30) NOT NULL,");
            sql.Append(@"`CreateTime` varchar(30) Not NULL,");
            sql.Append(@"`LastModifyTime` varchar(30) Not NULL,");
            for (int i = 0 ; i < dt.Rows.Count ; i++)
            {
                sql.Append(@"`"+ dt.Rows[i]["zoneid"].ToString() +"` varchar(10) default '0',");
            }
            sql.Append(@"`isUsable` int(1) Default 1");
            sql.Append(@") COLLATE 'utf8_general_ci';");
            return DBBase.CreateTable(sql.ToString());
        }


        #endregion

        #region 工艺参数数据


        public static DataTable GetParameterData(DateTime StartTime , DateTime EndTime)
        {
            int years=EndTime.Year-StartTime.Year+1;
            StringBuilder cmd=new StringBuilder();
            for (int i = 0 ; i < years ; i++)
            {
                cmd.Append("select *");
                cmd.Append(" from `his_parameter_").Append(StartTime.Year + i).Append("` ");
                cmd.Append("where `savetime`>='").Append(StartTime.ToString("yyyy-MM-dd HH:mm:ss")).Append("' and `savetime`<='").Append(EndTime.ToString("yyyy-MM-dd HH:mm:ss")).Append("' ");
                cmd.Append("union all ");
            }
            if (cmd.Length < 11) return null;
            cmd.Remove(cmd.Length - 11 , 11).Append(" order by `savetime`");
            return DBBase.GetDataTable(cmd.ToString());
        }
        public static DataTable GetParameterData(DateTime StartTime,DateTime EndTime,List<int> ZoneIDs)
        {
            if (ZoneIDs == null || ZoneIDs.Count == 0) return null;
            List<int>zoneid=new List<int>();
            for (int i = 0 ; i < ZoneIDs.Count ; i++)
            {
                if (ZoneIDs[i] != 0)
                    zoneid.Add(ZoneIDs[i]);
            }
            if (zoneid.Count == 0) return null;
            int years=EndTime.Year-StartTime.Year+1;
            StringBuilder cmd=new StringBuilder();
            for (int i = 0 ; i < years ; i++)
            {
                cmd.Append("select `savetime`,");
                for (int j = 0 ; j < zoneid.Count ; j++)
                {
                    if(zoneid[j]!=0)
                    cmd.Append("`").Append(zoneid[j]).Append("`,");
                }
                cmd.Remove(cmd.Length - 1 , 1);
                cmd.Append(" from `his_parameter_").Append(StartTime.Year + i).Append("` ");
                cmd.Append("where `savetime`>='").Append(StartTime.ToString("yyyy-MM-dd HH:mm:ss")).Append("' and `savetime`<='").Append(EndTime.ToString("yyyy-MM-dd HH:mm:ss")).Append("' ");
                cmd.Append("union all ");
            }
            if (cmd.Length < 11) return null;
            cmd.Remove(cmd.Length - 11 , 11).Append(" order by `savetime`");
            return DBBase.GetDataTable(cmd.ToString());
        }

        //保存历史数据，按天进行保存pv，sv，ovpv
        public static bool SaveParameterData(Dictionary<int , double> Values)
        {
            string tablename="his_parameter_"+DateTime.Now.Date.ToString("yyyy");
            if (CreateParameterDataTable(tablename))
            {
                return DBBase.ExecuteSqlTran(new List<string>() { DBBase.CreateSQLCommand(tablename , Values).ToString() });
            }
            return false;
        }
        public static bool SaveParameterDataByDay(Dictionary<int, double> Values)
        {
            string tablename = "his_parameter_" + DateTime.Now.Date.ToString("yyyyMMdd");
            if (CreateParameterDataTable(tablename))
            {
                return DBBase.ExecuteSqlTran(new List<string>() { DBBase.CreateSQLCommand(tablename, Values).ToString() });
            }
            return false;
        }
        private static bool CreateParameterDataTable(string table)
        {
            //这里需要对表的结构进行验证，如果表结构不同，就创建新的表
            if (DBBase.CheckTable(table)) return true;
            StringBuilder sql=new StringBuilder(@"Select `pvid`,`svid`,`ovpvid` from set_zone order by zoneID");
            DataTable dt=DBBase.GetDataTable(sql.ToString());
            if (dt != null && dt.Rows.Count > 0)
            {
                sql = new StringBuilder(@"CREATE TABLE IF NOT EXISTS `" + table + "` (");
                sql.Append(@"`SaveTime` varchar(255) NOT NULL,");
                for (int i = 0 ; i < dt.Rows.Count ; i++)
                {
                    if (dt.Rows[i]["pvid"].ToString().Trim() != "" && dt.Rows[i]["pvid"].ToString().Trim() != "0")
                        sql.Append("`").Append(dt.Rows[i]["pvid"]).Append("`").Append(" varchar(10) NULL,");
                    if (dt.Rows[i]["svid"].ToString().Trim() != "" && dt.Rows[i]["svid"].ToString().Trim() != "0")
                        sql.Append("`").Append(dt.Rows[i]["svid"]).Append("`").Append(" varchar(10) NULL,");
                    if (dt.Rows[i]["ovpvid"].ToString().Trim() != "" && dt.Rows[i]["ovpvid"].ToString().Trim() != "0")
                        sql.Append("`").Append(dt.Rows[i]["ovpvid"]).Append("`").Append(" varchar(10) NULL,");
                }
                sql = new StringBuilder(sql.ToString(0 , sql.Length - 1));
                sql.Append(@") COLLATE 'utf8_general_ci';");
                return DBBase.CreateTable(sql.ToString());
            }
            return false;
        }
        #endregion

        #region 保存设定参数数据
        public static bool SaveDiffSettings(Dictionary<int , double> Values)
        {
            string tablename="his_Setting_Diff";
            if (CreateDiffSettingsTable(tablename))
            {
                return DBBase.ExecuteSqlTran(new List<string>() { DBBase.CreateSQLCommand(tablename , Values).ToString() });
            }
            return false;
        }
        private static bool CreateDiffSettingsTable(string table)
        {
            if (DBBase.CheckTable(table)) return true;
            StringBuilder sql=new StringBuilder(@"Select `diffid` from set_zone order by zoneID");
            DataTable dt=DBBase.GetDataTable(sql.ToString());
            if (dt != null && dt.Rows.Count > 0)
            {
                sql = new StringBuilder(@"CREATE TABLE IF NOT EXISTS `" + table + "` (");
                sql.Append(@"`SaveTime` varchar(255) NOT NULL,");
                for (int i = 0 ; i < dt.Rows.Count ; i++)
                {
                    if (dt.Rows[i]["diffid"].ToString().Trim() != "")
                        sql.Append("`").Append(dt.Rows[i]["diffid"]).Append("`").Append(" varchar(255) NULL,");
                }
                sql = new StringBuilder(sql.ToString(0 , sql.Length - 1));
                sql.Append(@") COLLATE 'utf8_general_ci';");
                return DBBase.CreateTable(sql.ToString());
            }
            return false;
        }
        public static bool SaveBaisSettings(Dictionary<int , double> Values)
        {
            string tablename="his_Setting_Bais";
            if (CreateBaisSettingsTabel(tablename))
            {
                return DBBase.ExecuteSqlTran(new List<string>() { DBBase.CreateSQLCommand(tablename , Values).ToString() });
            }
            return false;
        }
        private static bool CreateBaisSettingsTabel(String table)
        {
            if (DBBase.CheckTable(table)) return true;
            StringBuilder sql=new StringBuilder(@"Select `ubaisID`,`lbaisID` from set_zone order by zoneID");
            DataTable dt=DBBase.GetDataTable(sql.ToString());
            if (dt != null && dt.Rows.Count > 0)
            {
                sql = new StringBuilder(@"CREATE TABLE IF NOT EXISTS `" + table + "` (");
                sql.Append(@"`SaveTime` varchar(255) NOT NULL,");
                for (int i = 0 ; i < dt.Rows.Count ; i++)
                {
                    if (dt.Rows[i]["ubaisID"].ToString().Trim() != "")
                        sql.Append("`").Append(dt.Rows[i]["ubaisID"]).Append("`").Append(" varchar(255) NULL,");
                    if (dt.Rows[i]["lbaisID"].ToString().Trim() != "")
                        sql.Append("`").Append(dt.Rows[i]["lbaisID"]).Append("`").Append(" varchar(255) NULL,");
                }
                sql = new StringBuilder(sql.ToString(0 , sql.Length - 1));
                sql.Append(@") COLLATE 'utf8_general_ci';");
                return DBBase.CreateTable(sql.ToString());
            }
            return false;
        }
        public static bool SaveOverHeatSetPoint(Dictionary<int , double> Values)
        {
            string tablename="his_Setting_OVSV";
            if (CreateOverHeatSetpointTable(tablename))
            {
                return DBBase.ExecuteSqlTran(new List<string>() { DBBase.CreateSQLCommand(tablename , Values).ToString() });
            }
            return false;
        }
        private static bool CreateOverHeatSetpointTable(string table)
        {
            if (DBBase.CheckTable(table)) return true;
            StringBuilder sql=new StringBuilder(@"Select `ovsvid` from set_zone order by zoneID");
            DataTable dt=DBBase.GetDataTable(sql.ToString());
            if (dt != null && dt.Rows.Count > 0)
            {
                sql = new StringBuilder(@"CREATE TABLE IF NOT EXISTS `" + table + "` (");
                sql.Append(@"`SaveTime` varchar(255) NOT NULL,");
                for (int i = 0 ; i < dt.Rows.Count ; i++)
                {
                    if (dt.Rows[i]["ovsvid"].ToString().Trim() != "")
                        sql.Append("`").Append(dt.Rows[i]["ovsvid"]).Append("`").Append(" varchar(255) NULL,");
                }
                sql = new StringBuilder(sql.ToString(0 , sql.Length - 1));
                sql.Append(@") COLLATE 'utf8_general_ci';");
                return DBBase.CreateTable(sql.ToString());
            }
            return false;
        }
        #endregion

        #region 批次相关

        public static DataTable GetBatchs()
        {
            if (DBBase.CheckTable("his_batch"))
            {
                StringBuilder cmd=new StringBuilder("select * from `his_batch` where `isbatchrunning`='1' order by `createtime`;" );
                return DBBase.GetDataTable(cmd.ToString());
            }
            return null;
        }
        public static bool SaveLoadInfo()
        {
            return true;
        }

        public static bool SaveBatchInfo(StringBuilder BatchSerial , Dictionary<string ,string> Values)
        {
            if (CreateBatchTable("his_batch"))
            {
                StringBuilder cmd=new StringBuilder("select * from `his_batch` where `batchserial`='").Append( BatchSerial).Append("';");
                
                DataTable dt=DBBase.GetDataTable(cmd.ToString());
                if(dt==null || dt.Rows.Count == 0)//添加批次
                {
                    StringBuilder sbvalues=new StringBuilder(") values ('").Append(BatchSerial).Append("',");
                    cmd = new StringBuilder("insert into `his_batch` (`batchserial`,");
                    foreach (string item in Values.Keys)
                    {
                        cmd.Append("`").Append(item).Append("`,");
                        sbvalues.Append("'").Append(Values[item]).Append("',");
                    }
                    cmd.Remove(cmd.Length - 1 , 1).Append(sbvalues.Remove(sbvalues.Length - 1 , 1)).Append(");");
                }
                else
                {
                    cmd = new StringBuilder("update `his_batch` set ");
                    foreach (string item in Values.Keys)
                    {
                        cmd.Append("`").Append(item).Append("`='").Append(Values[item]).Append("',");
                    }
                    cmd.Remove(cmd.Length - 1 , 1).Append(" where `batchserial`='").Append(BatchSerial).Append("';");
                }
                return DBBase.ExecuteSqlTran(new List<string>() { cmd.ToString() });
            }
            return false;
        }
        private static bool CreateBatchTable(string table)
        {
            if (DBBase.CheckTable(table)) return true;
            StringBuilder  sql = new StringBuilder(@"CREATE TABLE IF NOT EXISTS `").Append(table).Append("`(");
            sql.Append(@"`batchserial` varchar(20) NOT NULL,");
            sql.Append(@"`createtime` varchar(20) NULL,");
            sql.Append(@"`stoptime` varchar(20) NULL,");
            sql.Append(@"`isbatchrunning` int(1) default 0,");
            sql.Append(@"`endtime` varchar(20) NULL,");
            sql.Append(@"`createuser` varchar(64) NULL,");
            sql.Append(@"`productversion` varchar(20) NULL,");
            for (int i = 0 ; i < 10 ; i++)
            {
                sql.Append(@"`info").Append(i).Append("` varchar(128) NULL,");
            }
            sql.Remove(sql.Length - 1 , 1);
            sql.Append(@") COLLATE 'utf8_general_ci';");
            return DBBase.CreateTable(sql.ToString());
        }
        #endregion

        #region 获取相关设置


        public static DataTable getBoxColorSet()
        {
            if (DBBase.CheckTable("set_boxcolor"))
            {
                return DBBase.GetDataTable(@"Select * from `set_boxcolor` order by `id`");
            }
            return null;
        }
        public static DataTable getMessageInfos()
        {
            if (DBBase.CheckTable("set_messageinfo"))
            {
                return DBBase.GetDataTable(@"Select * from `set_messageinfo` order by `id`");
            }
            return null;
        }

        public static string GetAlarmInfoFilename(int AlarmType)
        {
            DataTable dt=DBBase.GetDataTable("select * from `set_alarmhelp` where `altypeid`='"+ AlarmType +"';");
            if (dt == null || dt.Rows.Count == 0) return "";
            return dt.Rows[0]["altypename"].ToString();
        }
        public static DataTable GetAlarmLevelColorSet()
        {
            if (DBBase.CheckTable("set_alarm_color"))
            {
                return DBBase.GetDataTable(@"Select * from `set_alarm_color` where `isuse`='1' order by `allevel`");
            }
            return null;
        }

        public static DataTable GetKeyTimeSet()
        {
            if (DBBase.CheckTable("set_keytime"))
            {
                return DBBase.GetDataTable(@"Select * from set_keytime order by keytimeid");
            }
            return null;
        }
        public static DataTable GetEquipSet()
        {
            if (DBBase.CheckTable("set_equip"))
            {
                return DBBase.GetDataTable(@"Select * from set_equip order by equipid");
            }
            return null;
        }

        public static string[] GetAlarmEquipUsed()
        {
            if (DBBase.CheckTable("set_alarm"))
            {
                DataTable dt= DBBase.GetDataTable(@"Select distinct Equipid from set_alarm where equipid!='' order by equipid");
                if (dt == null || dt.Rows.Count == 0) return null;
                string[] v=new string[dt.Rows.Count];
                for (int i = 0 ; i < dt.Rows.Count ; i++)
                {
                    v[i] = dt.Rows[i]["equipid"].ToString();
                }
                return v;
            }
            return null;
        }
        public static string[] GetZoneMonitorGroupUsed()
        {
            string cmd=@"select distinct groupid from set_zone order by groupid";
            DataTable dt= DBBase.GetDataTable(cmd);
            if (dt.Rows.Count > 0)
            {
                string[] v=new string[dt.Rows.Count];
                for (int i = 0 ; i < dt.Rows.Count ; i++)
                {
                    v[i] = dt.Rows[i]["groupid"].ToString();
                }
                return v;
            }
            return null;
        }



        public static string [] GetDesignGroupUsed()
        {
            string cmd=@"select distinct groupid from set_zone where `groupid`>0 order by groupid";
            DataTable dt= DBBase.GetDataTable(cmd);
            if(dt.Rows.Count>0)
            {
                string[] v=new string[dt.Rows.Count];
                for (int i = 0 ; i < dt.Rows.Count ; i++)
                {
                    v[i] = dt.Rows[i]["groupid"].ToString();
                }
                return v;
            }
            return null;
        }

        public static DataTable GetSetPointSet()
        {
            if (DBBase.CheckTable("set_zone"))
            {
                return DBBase.GetDataTable(@"Select * from set_zone where `groupid`>0 and `issv`=1 and `svtype`<10 order by zoneID");
            }
            return null;
        }
        public static DataTable GetDesignGroupSet()
        {
            string cmd=@"select * from set_designgroup order by groupid";
            return DBBase.GetDataTable(cmd);
        }


        public static DataTable GetAlarmSet()
        {
            if (DBBase.CheckTable("set_alarm"))
            {
                return DBBase.GetDataTable(@"Select * from set_alarm where equipid!='' order by alID");
            }
            return null;
        }
        public static DataTable GetZoneSet(string zoneid="")
        {
            if (DBBase.CheckTable("set_zone"))
            {
                if(zoneid!="")
                    return DBBase.GetDataTable(@"Select * from set_zone where `zoneid`='"+ zoneid + "' order by zoneID");
                else
                    return DBBase.GetDataTable(@"Select * from set_zone order by zoneID");
            }
            return null;
        }
        public static DataTable GetItemSet()
        {
            if (DBBase.CheckTable("set_item"))
            {
                return DBBase.GetDataTable(@"Select * from set_item order by itemID");
            }
            return null;
        }
        #endregion

        #region 物料信息
        //从数据库中获取box是否存在
        public static DataTable FindProcessBox(int BoxSerial ,int mark,DateTime loadtime)
        {
            string tablename="his_ProcessBox_" + loadtime.ToString("yyyyMMdd");
            StringBuilder cmd=new StringBuilder("select * from ").Append(tablename).Append(" where `box`='").Append(BoxSerial).Append("' and `mark`='").Append(mark).Append("';");
            return DBBase.GetDataTable(cmd.ToString());
        }


        public static DataTable GetProcessBoxByBatchserial(string batchserial)
        {
            if (!DBBase.CheckTable("his_batch")) return null;
            StringBuilder cmd=new StringBuilder("select * from `his_batch` where `batchserial`='").Append(batchserial).Append("';");
            DataTable dt=DBBase.GetDataTable(cmd.ToString());
            if (dt == null || dt.Rows.Count == 0) return null;
            DateTime starttime,endtime;
            if (!DateTime.TryParse(dt.Rows[0]["createtime"].ToString() , out starttime))
                starttime = Convert.ToDateTime("2021-1-1 0:0:0");
            if (!DateTime.TryParse(dt.Rows[0]["stoptime"].ToString() , out endtime))
                endtime = DateTime.Now;
            cmd.Clear();
            int days=endtime.Subtract(starttime).Days;
            for (int i = 0 ; i <= days ; i++)
            {
                if (DBBase.CheckTable("his_ProcessBox_" + starttime.AddDays(i).ToString("yyyyMMdd")))
                    cmd.Append("select * from `his_ProcessBox_").Append(starttime.AddDays(i).ToString("yyyyMMdd")).Append("` where `batchserial`= '").Append(batchserial).Append("'").Append(" union all ");
            }
            if(cmd.Length>11) cmd.Remove(cmd.Length - 11 , 11).Append("; ");
            //cmd = new StringBuilder("select * from ").Append(tables).Append(" where `batchserial`='").Append(batchserial).Append("';");
            return DBBase.GetDataTable(cmd.ToString());
        }

        public static DataTable GetProcessBoxByTime(DateTime StartTime,DateTime EndTime)
        {
            StringBuilder cmd=new StringBuilder();
            int days=EndTime.Subtract(StartTime).Days;
            for (int i = 0; i <= days; i++)
            {
                StringBuilder sbtable=new StringBuilder("his_ProcessBox_").Append( StartTime.AddDays(i).ToString("yyyyMMdd"));
                if (DBBase.CheckTable(sbtable.ToString()))
                {
                    cmd.Append("Select ")
                        .Append(sbtable).Append(".box,")
                        .Append(sbtable).Append(".mark,")
                        .Append(sbtable).Append(".batchserial,")
                        .Append(sbtable).Append(".loadtime,");
                    for (int j = 0 ; j < 40 ; j++)
                    {
                        cmd.Append(sbtable).Append(".keytime").Append(j).Append(",");
                    }
                    cmd.Append("his_batch.productversion,")
                        .Append("his_batch.createuser,")
                        .Append("pro_productinfo.info0")
                        .Append(" from `").Append(sbtable).Append("` ")
                        .Append("left join `his_batch`")
                        .Append("on ").Append(sbtable).Append(".batchserial = his_batch.batchserial ")
                        .Append("left join `pro_productinfo` ")
                        .Append("on his_batch.productversion= pro_productinfo.productversion ")
                        .Append("where `loadtime`>='").Append(StartTime.ToString("yyyy-MM-dd HH:mm:ss"))
                        .Append("' and `loadtime`<='").Append(EndTime.ToString("yyyy-MM-dd HH:mm:ss")).Append("' union all ");

                }
            }
            if (cmd.Length > 11)
            {
                cmd.Remove(cmd.Length - 11 , 11).Append(" order by loadtime");
                return DBBase.GetDataTable(cmd.ToString());
            }
            return null;
        }


        public static bool ChangeBoxSerial(int oldBoxSerial , int newBoxSerial , int mark,DateTime loadtime)
        {
            string tablename="his_ProcessBox_" + loadtime.ToString("yyyyMMdd");
            if (DBBase.CheckTable(tablename))
            {
                StringBuilder cmd = new StringBuilder("UPDATE ").Append(tablename).Append(" SET `box`='").Append(newBoxSerial).Append("' where `box`='").Append(oldBoxSerial).Append("' and `mark`='").Append(mark).Append("';");
                return DBBase.ExecuteSqlTran(new List<string>() { cmd.ToString() });
            }
            return false;
        }


        public static void SaveProcessBoxInfoToFile(int BoxSerial, int mark, DateTime loadtime, Dictionary<string, string> values = null)
        {
            try
            {
                Ini boxinfofile = new Ini();
                string _fullfilename = Application.StartupPath + "\\Recorddata\\" + loadtime.Year + "\\" + loadtime.Month.ToString().PadLeft(2, '0') + "\\ProcessBox\\";
                if (!Directory.Exists(_fullfilename.ToString())) { Directory.CreateDirectory(_fullfilename.ToString()); }
                boxinfofile.Filename= _fullfilename + "Box" + loadtime.ToString("yyyy-MM-dd") + ".dat";
                string section = "Box" + BoxSerial.ToString().PadLeft(4, '0') + mark.ToString().PadLeft(4, '0');
                foreach (var item in values.Keys)
                    boxinfofile.Write(section, item, values[item]);
            }
            catch (Exception){
                throw;
            }
        }

        public static bool SaveProcessBoxInfo(int BoxSerial , int mark,DateTime loadtime, Dictionary<string , string> values = null)
        {
            if (values == null) return false;
            SaveProcessBoxInfoToFile(BoxSerial, mark, loadtime, values);
            string tablename="his_ProcessBox_" + loadtime.ToString("yyyyMMdd");
            if (CreateProcessBoxTable(tablename))
            {
                StringBuilder cmd=new StringBuilder("select * from ").Append(tablename).Append(" where box='").Append(BoxSerial).Append("' and mark='").Append(mark).Append("';");
                DataTable dt=DBBase.GetDataTable(cmd.ToString());
                if (dt != null && dt.Rows.Count > 0)
                {
                    cmd = new StringBuilder("UPDATE ").Append(tablename).Append(" SET ");
                    foreach (string item in values.Keys)
                    {
                        cmd.Append("`").Append(item).Append("`='").Append(values[item]).Append("',");
                    }
                    cmd.Remove(cmd.Length - 1 , 1);
                    cmd.Append(" where box='").Append(BoxSerial).Append("' and mark='").Append(mark).Append("';");
                }
                else
                {
                    cmd = new StringBuilder("insert into `").Append(tablename).Append("` (`box`,`mark`,`loadtime`,");
                    StringBuilder cmd2=new StringBuilder(")values('").Append(BoxSerial).Append("','").Append(mark).Append("','").Append(loadtime.ToString("yyyy-MM-dd HH:mm:ss")).Append("',");
                    foreach (string item in values.Keys)
                    {
                        cmd.Append("`").Append(item).Append("`,");
                        cmd2.Append("'").Append(values[item]).Append("',");
                    }
                    cmd.Remove(cmd.Length - 1 , 1);
                    cmd2.Remove(cmd2.Length - 1 , 1);
                    cmd.Append(cmd2).Append(");");
                }
                return DBBase.ExecuteSqlTran(new List<string>() { cmd.ToString() });
            }
            return false;
        }

        private static bool CreateProcessBoxTable(string table)
        {
            if (DBBase.CheckTable(table)) return true;
            StringBuilder  sql = new StringBuilder(@"CREATE TABLE IF NOT EXISTS `"+ table +"`(");
            sql.Append(@"`box` varchar(8) NOT NULL,");
            sql.Append(@"`mark` varchar(8) NOT NULL,"); //该字段由上位机生成，并记录到PLC
            sql.Append(@"`batchserial` varchar(20) NULL,");//关联批次
            sql.Append(@"`loadtime` varchar(20) NULL,");//该字段由上位机记录
            sql.Append(@"`errorcode` varchar(8) NULL,");//错误代码
            sql.Append(@"`quenchmoveintime` varchar(10) NULL,");//淬火时间
            sql.Append(@"`weight` varchar(10) NULL,");//物料重量
            for (int i = 0 ; i < 40 ; i++)
            {
                sql.Append(@"`keytime").Append(i).Append("` varchar(20) NULL,");//关键时间
            }
            sql.Remove(sql.Length - 1 , 1);
            sql.Append(@") COLLATE 'utf8_general_ci';");
            return DBBase.CreateTable(sql.ToString());
        }

        #endregion

        #region 操作事件
        public static bool SaveEvent(Dictionary<string,string> events)
        {
            string tablename="his_event_"+ DateTime.Now.Date.ToString("yyyy");
            if (CreatEventTable(tablename))
            {
                return DBBase.ExecuteSqlTran(new List<string>() { DBBase.CreateSQLCommand(tablename , events).ToString() });
            }
            return false;
        }

        public static Dictionary<string , string> dictEvent { get; } = new Dictionary<string , string>()
        {
            { "eventtype",""},
            { "eventserial",""},
            { "username",""},
            { "eventname",""},
            { "content1",""},
            { "content2",""}
        };
        private static bool CreatEventTable(string table)
        {
            if (DBBase.CheckTable(table)) return true;
            StringBuilder  sql = new StringBuilder(@"CREATE TABLE IF NOT EXISTS `his_event_"+ table+" `(");
            sql.Append(@"`SaveTime` varchar(255) NOT NULL,");
            sql.Append(@"`eventtype` varchar(255) NOT NULL,");
            sql.Append(@"`eventserial` varchar(255) NOT NULL,");
            sql.Append(@"`username` varchar(255) NOT NULL,");
            sql.Append(@"`eventname` varchar(255) NOT NULL,");
            sql.Append(@"`content1` varchar(255) NOT NULL,");
            sql.Append(@"`content2` varchar(255) NOT NULL");
            sql.Append(@") COLLATE 'utf8_general_ci';");
            return DBBase.CreateTable(sql.ToString());
        }
        #endregion
    }
}
